# This file is used to create the user_info_ext table in the database.

'''
-- 短信发送时间段
CREATE TABLE IF NOT EXISTS sms_time_slots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    start_time VARCHAR(50) NOT NULL,
    end_time VARCHAR(50) NOT NULL,
    current_status VARCHAR(50) DEFAULT 'active', -- active, blocked
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

'''
import logging
import datetime
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class SmsTimeSlotsMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_sms_time_slots(self, start_time, end_time):
        # print(start_time, end_time)

        sql = "INSERT INTO sms_time_slots (start_time, end_time) VALUES (?, ?)"
        values = (start_time, end_time)
        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info("Inserted SMS time slots successfully")

    def block_sms_time_slots(self): # Block all SMS time slots

        sql = "UPDATE sms_time_slots SET current_status = 'blocked' WHERE current_status = 'active'"

        self.cursor.execute(sql)
        self.conn.commit()
        logger.info("Blocked all SMS time slots successfully")

    

    # 查询所有激活的短信发送时间段
    def select_sms_time_slots_by_status_active(self) -> list: # Select all SMS time slots by status
            
        sql = "SELECT * FROM sms_time_slots WHERE current_status = 'active'"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        for result in results:
            # 将 created_at 字段从 UTC 时间转换为北京时间
            utc_time = datetime.datetime.strptime(result["created_at"], '%Y-%m-%d %H:%M:%S')
            beijing_time = utc_time + datetime.timedelta(hours=8)
            result["created_at"] = beijing_time.strftime('%Y-%m-%d %H:%M:%S')
        return results

    # 查询所有短信发送时间段
    def select_all_sms_time_slots(self): # Select all SMS time slots
            
        sql = "SELECT * FROM sms_time_slots"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        for result in results:
            # 将 created_at 字段从 UTC 时间转换为北京时间
            utc_time = datetime.datetime.strptime(result["created_at"], '%Y-%m-%d %H:%M:%S')
            beijing_time = utc_time + datetime.timedelta(hours=8)
            result["created_at"] = beijing_time.strftime('%Y-%m-%d %H:%M:%S')
        return results
    
    def delete_sms_time_slots_by_id(self, id): # Delete SMS time slot by id

        sql = "DELETE FROM sms_time_slots WHERE id = ?"
        values = (id,)
        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info(f"Updated SMS time slot with id: {id}, status: blocked")


    def update_sms_time_slots_status_by_id(self, id, status): # Update SMS time slot by id

        sql = "UPDATE sms_time_slots SET current_status = ? WHERE id = ?"
        values = (status, id)
        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info(f"Updated SMS time slot with id: {id}, status: {status}")

    def update_sms_time_slots_time_by_id(self, id, start_time, end_time): # Update SMS time slot by id


        sql = "UPDATE sms_time_slots SET start_time = ?, end_time = ? WHERE id = ?"
        values = (start_time, end_time, id)
        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info(f"Updated SMS time slot with id: {id}, start_time: {start_time}, end_time: {end_time}")